package app;
import java.sql.*;
import java.util.ArrayList;

/**
 * 数据库操作类，实例化此类来对数据库中的数据进行相关操作
 */

public class PlayerDB {
    private String userName = "";
    private String password = "";
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

    /**
     * 构造方法，使用前请先验证用户名密码是否正确
     * @param userName 用户名
     * @param password 密码
     * @throws SQLException
     */
    public PlayerDB(String userName, String password) throws SQLException {
        this.userName = userName;
        this.password = password;
        connection = DBTool.getConnection(userName);
    }

    /**
     * 传入用户输入的值，创建一个新的歌单
     * @param nameOfList 新歌单的名字
     * @return  返回为真：创建成功；返回为假：创建失败
     */
    public boolean addAList(String nameOfList) throws SQLException {
        boolean isAdded = false;
        try{
            String sql = "create table `" + nameOfList + "` like defaultList";       //创建与默认表结构相同的表
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.execute();
            //查询创建的表是否添加到数据库中
            String sql1 = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=? and TABLE_NAME=?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1,userName);
            preparedStatement.setString(2,nameOfList);
            resultSet = preparedStatement.executeQuery();
            if(resultSet!=null)
            while(resultSet.next()){
                isAdded = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return isAdded;
    }

    /**
     * 传入用户的值，删除指定歌单
     * @param nameOfList 歌单名
     * @return 返回为真：删除成功；返回为假：删除失败
     */
    public boolean removeAList(String nameOfList) {
        boolean isRemoved = true;
        try{
            String sql = "drop table `" + nameOfList + "`";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.execute();
            String sql1 = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=? and TABLE_NAME=?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setString(1,userName);
            preparedStatement.setString(2,nameOfList);
            resultSet = preparedStatement.executeQuery();
            if(resultSet!=null) while(resultSet.next()) isRemoved = false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return isRemoved;
    }

    /**
     *此函数用来获取一个歌单的所有歌曲的信息，传入歌单名字即可
     *每一首歌曲的信息以字符串形式展现，格式为：name_singer_lyrics_url_time
     *所有歌曲的信息都存放在一个ArrayList<String>中，可分别获得每一首歌曲的信息
     * @param nameOfList 指定歌单名字
     * @return 返回一个ArrayList<String>，当中一条条存的是歌曲的信息
     */
    public ArrayList<String> getInfoOfSongs(String nameOfList) {
        ArrayList<String> songsInfo = new ArrayList<String>();
        try {
            String sql1 = "select * from " + nameOfList;
            preparedStatement = connection.prepareStatement(sql1);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                String song = resultSet.getString("name") + "_"
                        + resultSet.getString("singer") + "_"
                        +resultSet.getString("lyrics") + "_"
                        +resultSet.getString("url") + "_"
                        +resultSet.getString("time") + "\n";
                songsInfo.add(song);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return songsInfo;
    }

    /**
     * 传入一个列表的名字，用来获取一个指定列表的所有歌曲信息
     * @param nameOfList 列表名
     * @return ArrayList<ArrayList<String>>，集合里嵌套了一个集合，里面的集合包含了一首歌曲的：name，singer，lyrics，url，time
     */
    public ArrayList<ArrayList<String>> test(String nameOfList) {
        ArrayList<ArrayList<String>> allSongs = new ArrayList<>();
        try {
            String sql0 = "select * from " + nameOfList;
            preparedStatement = connection.prepareStatement(sql0);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                ArrayList<String> list = new ArrayList<>();
                list.add(resultSet.getString("name"));
                list.add(resultSet.getString("singer"));
                list.add(resultSet.getString("lyrics"));
                list.add(resultSet.getString("url"));
                list.add(resultSet.getString("time"));
                allSongs.add(list);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return allSongs;
    }


    /**
     *将指定歌曲添加到指定歌单
     * @param song 歌曲对象
     * @param nameOfList 歌单名
     * @return 返回为真：添加成功；返回为假，添加失败
     */
    public boolean addSongToList(Song song, String nameOfList) {
        boolean isAdded = false;
        try{
            String sql0 = "insert into " + nameOfList + " set name = ?, singer = ?, lyrics = ?, url = ?, time = ?";
            preparedStatement = connection.prepareStatement(sql0);
            preparedStatement.setString(1,song.getName());
            preparedStatement.setString(2,song.getSinger());
            preparedStatement.setString(3,song.getLyrics());
            preparedStatement.setString(4,song.getURL());
            preparedStatement.setString(5,song.getTime());
            preparedStatement.execute();
            String sql1 = "select * from " + nameOfList + "where name = ?, singer = ?, lyrics = ?, url = ?, time = ?";
            preparedStatement.setString(1,song.getName());
            preparedStatement.setString(2,song.getSinger());
            preparedStatement.setString(3,song.getLyrics());
            preparedStatement.setString(4,song.getURL());
            preparedStatement.setString(5,song.getTime());
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) isAdded = true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return isAdded;
    }

    /**
     *从指定的歌单删除指定的歌曲
     * @param urlOfSong 歌曲的URL，唯一
     * @param nameOfList 列表名
     * @return 返回为真：删除成功；返回为假：删除失败
     */
    public boolean removeSongFromList(String urlOfSong,String nameOfList) {
        boolean isRemoved = false;
        try{
            String sql0 = "delete from " + nameOfList + "where url = ?";
            preparedStatement = connection.prepareStatement(sql0);
            preparedStatement.setString(1,urlOfSong);
            preparedStatement.execute();
            isRemoved = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return isRemoved;
    }

    /**
     * 返回这个用户的所有列表名
     * @return ArrayList<String> 每一个元素代表当前用户的一个列表名
     */
    public ArrayList<String> showAllLists() {
        ArrayList<String> lists = new ArrayList<String>();
        try{
            String sql = "show tables";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) lists.add(resultSet.getString(1));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lists;
    }

    /**
     * 对数据库的操作完成后执行此函数，用来关闭PlayerDB实例与数据库的连接
     */
    public void Close() {
        if(resultSet!=null) try {
            resultSet.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (preparedStatement!=null) try {
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(connection!=null) try {
            connection.close();
        } catch (Exception e) {
         e.printStackTrace();
        }
    }

}